﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class MONITOREODA

    Public Function Lista_Monitoreo() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT P.PERSONA , P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO AS MONITOREADOR  " & _
                      "FROM USUARIO U INNER JOIN PERSONA P ON P.USUARIO = U.USUARIO WHERE U.PERFIL='MO' AND U.ESTADO='A' "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function asig_monitor(ByVal usuario As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "select P.POA, P.NOMBRE from POA P " + _
                " INNER JOIN PERSONA ON  P.PERSONA_MONITOREO = PERSONA.PERSONA INNER JOIN USUARIO U ON PERSONA.USUARIO = U.USUARIO " + _
                "WHERE PERSONA.USUARIO ='" + usuario + "'"

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function proyectosmonitor(ByVal usuario As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select P.NOMBRE,POA from POA P " + _
            "INNER JOIN PERSONA ON P.PERSONA_MONITOREO = PERSONA.PERSONA " + _
            "INNER JOIN USUARIO U ON PERSONA.USUARIO = U.USUARIO WHERE U.USUARIO ='" + usuario + "'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function monitoreadores() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select persona,NOMBRES || ' ' || APELLIDO_PATERNO || ' ' || APELLIDO_MATERNO AS MONITOREADOR from persona inner join USUARIO ON PERSONA.USUARIO = USUARIO.USUARIO where USUARIO.PERFIL='MO' order by NOMBRES"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function carga_grilla(ByVal strPersona As String, ByVal strCodigoPlanTrabajoPersona As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            'lxc_SQL = "select P.ID_plan_trabajo_poa, o.NOMBRE,trabajo_campo,trabajo_gabinete, p.POA , E.DESCRIPCION from plan_trabajo_poa p " & _
            '    "inner join poa o on p.POA = o.POA INNER JOIN EJECUTOR E ON E.EJECUTOR = O.EJECUTOR where '" & strPersona & "' IN (O.PERSONA_MONITOREO, O.PERSONA_ESPECIALISTA) AND O.ESTADO = 'A'  AND P.ID_PLAN_TRABAJO_PERSONA =" & strCodigo

            'lxc_SQL = "SELECT P.ID_plan_trabajo_poa, o.NOMBRE,trabajo_campo,trabajo_gabinete, p.POA , E.DESCRIPCION " & _
            '        "FROM Plan_trabajo_poa P " & _
            '        "INNER JOIN poa o on P.POA = o.POA INNER JOIN EJECUTOR E ON E.EJECUTOR = O.EJECUTOR " & _
            '                    "WHERE O.ANHIO_FIRMA_POA = '" & strAnhioFirma & "' AND O.ESTADO = 'A'  AND P.ID_PLAN_TRABAJO_PERSONA = '" & strCodigo & "' " & _
            '        "ORDER BY Descripcion"

            lxc_SQL = "SELECT ID_plan_trabajo_poa, NOMBRE,trabajo_campo,trabajo_gabinete, POA , DESCRIPCION " & _
                    "FROM " & _
                    "( " & _
                    "SELECT P.ID_plan_trabajo_poa, o.NOMBRE,trabajo_campo,trabajo_gabinete, p.POA , E.DESCRIPCION " & _
                    "FROM Plan_trabajo_poa P " & _
                    "INNER JOIN poa o on P.POA = o.POA INNER JOIN EJECUTOR E ON E.EJECUTOR = O.EJECUTOR " & _
                    "WHERE O.ESTADO IN('A','C')  AND P.ID_PLAN_TRABAJO_PERSONA = '" & strCodigoPlanTrabajoPersona & "' " & _
                    "UNION " & _
                    "SELECT ID_PLAN_TRABAJO_POA, 'OTRAS TAREAS' NOMBRE, TRABAJO_CAMPO, TRABAJO_GABINETE, '  ' POA, 'ZZZ999' DESCRIPCION  " & _
                    "FROM PLAN_TRABAJO_POA PTP " & _
                    "WHERE PTP.ID_PLAN_TRABAJO_PERSONA = '" & strCodigoPlanTrabajoPersona & "' AND " & _
                    "           POA IS NULL " & _
                    ") " & _
                    "ORDER BY DESCRIPCION, NOMBRE ASC "

            'By Tigrito(24/06/2013)
            '            "WHERE '" & strPersona & "' IN (O.PERSONA_MONITOREO, O.PERSONA_ESPECIALISTA) AND O.ANHIO_FIRMA_POA = '" & strAnhioFirma & "' AND O.ESTADO = 'A'  AND P.ID_PLAN_TRABAJO_PERSONA = '" & strCodigo & "' " & _
            '"ORDER BY Descripcion"

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function carga_grillafinanciero() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT G.DESCRIPCION, E.DESCRIPCION FROM GENERICA_GASTOS G INNER JOIN ESPECIFICA_GASTO E ON G.GENERICA_GASTOS=E.GENERICA_GASTO"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function carga_txtObservaciones() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = " select observacion from plan_trabajo_persona"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Sub corregir_dato(ByVal tc As String, ByVal tg As String, ByVal idptpoa As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = " UPDATE PLAN_TRABAJO_POA  SET TRABAJO_CAMPO='" + tc + " ',TRABAJO_GABINETE='" + tg + "'WHERE ID_PLAN_TRABAJO_POA='" + idptpoa + "'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub Reactivar_Formato_02_03(ByVal strPoa As String, ByVal StrPeriodo As String)
        Dim objData As AdministradorOracle

        Dim pObjgrilla As String = "REACTIVAR_FORM_02_03"
        Try
            objData = New AdministradorOracle

            objData.CreateInputOracle("strPoa", strPoa, 20)
            objData.CreateInputOracle("StrPeriodo", StrPeriodo, 20)

            objData.ExecuteOracle_NonQuery("REACTIVAR_FORM_02_03", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub



    Public Sub agrega_proyecto(ByVal IDPER As Integer, ByVal v_POA As String, ByVal TC As String, ByVal TG As String, ByVal TP As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Dim IDPOA As Integer
        Try
            lxc_SQL = "select id_plan_trabajo_poA from (select id_plan_trabajo_poA FROM PLAN_TRABAJO_POA order by id_plan_trabajo_poa desc) "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            If objDt.Rows.Count > 0 Then
                lxc_SQL = lxc_SQL + " where rownum=1 "
                objDt = Nothing
                objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            End If
            If v_POA = "&nbsp;" Then
                v_POA = ""
            End If
            If objDt.Rows.Count = 0 Then
                IDPOA = 1
            Else
                IDPOA = CInt(objDt.Rows(0).Item("ID_PLAN_TRABAJO_POA").ToString) + 1
            End If
            objData.CreateInputOracle("IDPOA", IDPOA, 50)
            objData.CreateInputOracle("IDPER", IDPER, 50)
            objData.CreateInputOracle("v_POA", v_POA, 8)
            objData.CreateInputOracle("TC", TC, 2)
            objData.CreateInputOracle("TG", TG, 2)
            objData.CreateInputOracle("TP", TP, 1)
            objData.ExecuteOracle_NonQuery("INSERTARPLANTRABAJO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function buscarGrilla(ByVal Persona As String, ByVal Nombre As String, ByVal pper As String, ByVal strPeriodo As String, ByVal anhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = " SELECT PP.ABREVIATURA, " + _
                      " EJ.DESCRIPCION, " + _
                      " PO.NOMBRE, " + _
                      " PO.POA, " + _
                      " PO.ESTADO_REPROG, " + _
                      " VERIFICA_PROY_CONCLUIDO(PO.POA, PO.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " + _
                      " PO.VERSION, " + _
                      " PO.MONTO_TOTAL_INVERTIDO," + _
                      " PO.MONTO_INV_ANHIO," + _
                      " UB.NOMBRE UBIGEO," + _
                      " PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO MONITOREADOR, " + _
                      " PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO ESPECIALISTA," + _
                      " G.DESCRIPCION ESTADO_DES," + _
                      " PTPE.ESTADO, " + _
                      " CASE WHEN T1.ESTADO IS NULL " + _
                      "   THEN 'VACIO' " + _
                      "   ELSE " + _
                      "   CASE WHEN T1.ESTADO = '1' " + _
                      "          THEN 'INICIADO'" + _
                      "          ELSE " + _
                      "              CASE WHEN T1.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIS, " + _
                      "     CASE WHEN T2.ESTADO IS NULL" + _
                      "        THEN 'VACIO'" + _
                      "        ELSE" + _
                      "           CASE WHEN T2.ESTADO = '1' " + _
                      "           THEN 'INICIADO'" + _
                      "           ELSE " + _
                      "              CASE WHEN T2.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIN, PO.VERSION VERSION_POA_VIGENTE, T1.VERSION_POA_AVANCE_FIS, T2.VERSION_POA_AVANCE_FIN " + _
                      " FROM PLAN_TRABAJO_PERSONA PTPE" + _
                      " INNER JOIN PLAN_TRABAJO_POA PTPO" + _
                      " ON PTPO.ID_PLAN_TRABAJO_PERSONA = PTPE.ID_PLAN_TRABAJO_PERSONA" + _
                      " INNER JOIN POA PO" + _
                      " ON PO.POA = PTPO.POA " + _
                      " INNER JOIN GENERAL G" + _
                      " ON G.CODIGO = PTPE.ESTADO AND G.ID = 'ESTADO'" + _
                      " INNER JOIN PPER PP" + _
                      " ON PP.PPER = PO.PPER" + _
                      " INNER JOIN EJECUTOR EJ" + _
                      " ON EJ.EJECUTOR = PO .EJECUTOR" + _
                      " INNER JOIN PERSONA PM " + _
                      " ON PO.PERSONA_MONITOREO = PM.PERSONA" + _
                      " INNER JOIN PERSONA PE" + _
                      " ON PO.PERSONA_ESPECIALISTA = PE.PERSONA" + _
                      " INNER JOIN UBIGEO UB" + _
                      " ON UB.UBIGEO = PO.UBIGEO  " + _
                      " LEFT JOIN (SELECT DISTINCT PAFS.POA, " + _
                      "  FIS.ESTADO, FIS.VERSION VERSION_POA_AVANCE_FIS  " + _
                      "  FROM    POA_ACTIVIDAD_FISICA PAFS" + _
                      "  LEFT JOIN" + _
                      "  POA_FISICO_PERSONA FIS " + _
                      "     ON FIS.ID_POA_ACTIVIDAD_FISICA = PAFS.ID_POA_ACTIVIDAD_FISICA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIS.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "' " + _
                      "  ) T1" + _
                      "  ON T1.POA = PO.POA" + _
                      "  LEFT JOIN (SELECT DISTINCT PAFN.POA," + _
                      "  FIN.ESTADO, FIN.VERSION VERSION_POA_AVANCE_FIN " + _
                      " FROM    POA_ACTIVIDAD_FINANCIERA PAFN" + _
                      "  LEFT JOIN" + _
                      "  POA_FINANCIERO_PERSONA FIN " + _
                      "     ON FIN.ID_POA_ACTIVIDAD_FINANCIERA = PAFN.ID_POA_ACTIVIDAD_FINANCIERA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIN.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "'" + _
                      "  ) T2" + _
                      " ON T2.POA = PO.POA" + _
                      " WHERE PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
                      " AND '" & Persona & "' IN (PTPE.PERSONA_MONITOREO, " + _
                      " PTPE.JEFE_INMEDIATO,PO.PERSONA_ESPECIALISTA, PM.JEFE_INMEDIATO) " + _
                      " AND PTPE.PERIODO LIKE '" & strPeriodo & "%' " + _
                      " ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt


            '" WHERE '" & Persona & "' IN (PTPE.PERSONA_MONITOREO, PM.JEFE_INMEDIATO, " + _
            '" PTPE.JEFE_INMEDIATO,PO.PERSONA_ESPECIALISTA) AND PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
            '" AND PTPE.PERIODO LIKE '" & periodo & "%' "


            'If Nombre <> "" Then
            '    lxc_SQL = lxc_SQL & "AND UPPER(PO.NOMBRE) like UPPER('" & Nombre & "%') "
            'End If
            'If pper <> "all" Then
            '    lxc_SQL = lxc_SQL & "AND PO.PPER = '" & pper & "' "
            'End If
            'lxc_SQL = lxc_SQL & " ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC "
            'objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            'Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function buscarGrillaAdmin(ByVal strMonitoreador As String, ByVal strNombre As String, ByVal strPPER As String, ByVal strPeriodo As String, ByVal anhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = " SELECT PP.ABREVIATURA, " + _
                      " EJ.DESCRIPCION, " + _
                      " PO.NOMBRE, " + _
                      " PO.POA, " + _
                      " PO.ESTADO_REPROG, " + _
                      " VERIFICA_PROY_CONCLUIDO(PO.POA, PO.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " + _
                      " PO.VERSION VERSION_POA_VIGENTE, T1.VERSION_POA_AVANCE_FIS, T2.VERSION_POA_AVANCE_FIN, " + _
                      " PO.MONTO_TOTAL_INVERTIDO," + _
                      " PO.MONTO_INV_ANHIO," + _
                      " UB.NOMBRE UBIGEO," + _
                      " PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO MONITOREADOR, " + _
                      " PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO ESPECIALISTA," + _
                      " G.DESCRIPCION ESTADO_DES," + _
                      " PTPE.ESTADO, " + _
                      " CASE WHEN T1.ESTADO IS NULL " + _
                      "   THEN 'VACIO' " + _
                      "   ELSE " + _
                      "   CASE WHEN T1.ESTADO = '1' " + _
                      "          THEN 'INICIADO'" + _
                      "          ELSE " + _
                      "              CASE WHEN T1.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIS, " + _
                      "     CASE WHEN T2.ESTADO IS NULL" + _
                      "        THEN 'VACIO'" + _
                      "        ELSE" + _
                      "           CASE WHEN T2.ESTADO = '1' " + _
                      "           THEN 'INICIADO'" + _
                      "           ELSE " + _
                      "              CASE WHEN T2.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIN" + _
                      " FROM PLAN_TRABAJO_PERSONA PTPE" + _
                      " INNER JOIN PLAN_TRABAJO_POA PTPO" + _
                      " ON PTPO.ID_PLAN_TRABAJO_PERSONA = PTPE.ID_PLAN_TRABAJO_PERSONA" + _
                      " INNER JOIN POA PO" + _
                      " ON PO.POA = PTPO.POA " + _
                      " INNER JOIN GENERAL G" + _
                      " ON G.CODIGO = PTPE.ESTADO AND G.ID = 'ESTADO'" + _
                      " INNER JOIN PPER PP" + _
                      " ON PP.PPER = PO.PPER" + _
                      " INNER JOIN EJECUTOR EJ" + _
                      " ON EJ.EJECUTOR = PO .EJECUTOR" + _
                      " INNER JOIN PERSONA PM " + _
                      " ON PO.PERSONA_MONITOREO = PM.PERSONA" + _
                      " INNER JOIN PERSONA PE" + _
                      " ON PO.PERSONA_ESPECIALISTA = PE.PERSONA" + _
                      " INNER JOIN UBIGEO UB" + _
                      " ON UB.UBIGEO = PO.UBIGEO  " + _
                      " LEFT JOIN (SELECT DISTINCT PAFS.POA, " + _
                      "  FIS.ESTADO, FIS.VERSION VERSION_POA_AVANCE_FIS " + _
                      "  FROM    POA_ACTIVIDAD_FISICA PAFS" + _
                      "  LEFT JOIN" + _
                      "  POA_FISICO_PERSONA FIS " + _
                      "     ON FIS.ID_POA_ACTIVIDAD_FISICA = PAFS.ID_POA_ACTIVIDAD_FISICA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIS.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "' " + _
                      "  ) T1" + _
                      "  ON T1.POA = PO.POA" + _
                      "  LEFT JOIN (SELECT DISTINCT PAFN.POA," + _
                      "  FIN.ESTADO, FIN.VERSION VERSION_POA_AVANCE_FIN " + _
                      " FROM    POA_ACTIVIDAD_FINANCIERA PAFN" + _
                      "  LEFT JOIN" + _
                      "  POA_FINANCIERO_PERSONA FIN " + _
                      "     ON FIN.ID_POA_ACTIVIDAD_FINANCIERA = PAFN.ID_POA_ACTIVIDAD_FINANCIERA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIN.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "'" + _
                      "  ) T2" + _
                      " ON T2.POA = PO.POA" + _
                      " WHERE PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
                      " AND PTPE.PERIODO LIKE '" & strPeriodo & "%' "
            If strMonitoreador <> "all" Then
                lxc_SQL = lxc_SQL & "AND PO.PERSONA_MONITOREO LIKE '" & strMonitoreador & "%' "
            End If
            If strNombre <> "" Then
                lxc_SQL = lxc_SQL & "AND UPPER(PO.NOMBRE) LIKE UPPER('" & strNombre & "%') "
            End If
            If strPPER <> "all" Then
                lxc_SQL = lxc_SQL & "AND PO.PPER = '" & strPPER & "' ORDER BY PO.POA"
            Else
                lxc_SQL = lxc_SQL & "ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC"
            End If
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try

        '" WHERE PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
        '" AND '" & usuario & "' IN (PTPE.PERSONA_MONITOREO, " + _
        '" PTPE.JEFE_INMEDIATO,PO.PERSONA_ESPECIALISTA, PM.JEFE_INMEDIATO) " + _
        '" AND PTPE.PERIODO LIKE '" & strPeriodo & "%' " + _
        '" ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC "

    End Function

    Public Function filtrar_asig_monitor(ByVal usuario As String, ByVal strPeriodo As String, ByVal anhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = " SELECT PP.ABREVIATURA, " + _
                      " EJ.DESCRIPCION, " + _
                      " PO.NOMBRE, " + _
                      " PO.POA, " + _
                      " PO.ESTADO_REPROG, " + _
                      " VERIFICA_PROY_CONCLUIDO(PO.POA, PO.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " + _
                      " PO.MONTO_TOTAL_INVERTIDO," + _
                      " PO.MONTO_INV_ANHIO," + _
                      " UB.NOMBRE UBIGEO," + _
                      " PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO MONITOREADOR, " + _
                      " PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO ESPECIALISTA," + _
                      " G.DESCRIPCION ESTADO_DES," + _
                      " PTPE.ESTADO, " + _
                      " CASE WHEN T1.ESTADO IS NULL " + _
                      "   THEN 'VACIO' " + _
                      "   ELSE " + _
                      "   CASE WHEN T1.ESTADO = '1' " + _
                      "          THEN 'INICIADO'" + _
                      "          ELSE " + _
                      "              CASE WHEN T1.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIS, " + _
                      "     CASE WHEN T2.ESTADO IS NULL" + _
                      "        THEN 'VACIO'" + _
                      "        ELSE" + _
                      "           CASE WHEN T2.ESTADO = '1' " + _
                      "           THEN 'INICIADO'" + _
                      "           ELSE " + _
                      "              CASE WHEN T2.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIN, PO.VERSION VERSION_POA_VIGENTE, T1.VERSION_POA_AVANCE_FIS, T2.VERSION_POA_AVANCE_FIN " + _
                      " FROM PLAN_TRABAJO_PERSONA PTPE" + _
                      " INNER JOIN PLAN_TRABAJO_POA PTPO" + _
                      " ON PTPO.ID_PLAN_TRABAJO_PERSONA = PTPE.ID_PLAN_TRABAJO_PERSONA" + _
                      " INNER JOIN POA PO" + _
                      " ON PO.POA = PTPO.POA " + _
                      " INNER JOIN GENERAL G" + _
                      " ON G.CODIGO = PTPE.ESTADO AND G.ID = 'ESTADO'" + _
                      " INNER JOIN PPER PP" + _
                      " ON PP.PPER = PO.PPER" + _
                      " INNER JOIN EJECUTOR EJ" + _
                      " ON EJ.EJECUTOR = PO .EJECUTOR" + _
                      " INNER JOIN PERSONA PM " + _
                      " ON PO.PERSONA_MONITOREO = PM.PERSONA" + _
                      " INNER JOIN PERSONA PE" + _
                      " ON PO.PERSONA_ESPECIALISTA = PE.PERSONA" + _
                      " INNER JOIN UBIGEO UB" + _
                      " ON UB.UBIGEO = PO.UBIGEO  " + _
                      " LEFT JOIN (SELECT DISTINCT PAFS.POA, " + _
                      "  FIS.ESTADO, FIS.VERSION VERSION_POA_AVANCE_FIS " + _
                      "  FROM  POA_ACTIVIDAD_FISICA PAFS" + _
                      "  LEFT JOIN" + _
                      "  POA_FISICO_PERSONA FIS " + _
                      "     ON FIS.ID_POA_ACTIVIDAD_FISICA = PAFS.ID_POA_ACTIVIDAD_FISICA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIS.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "' " + _
                      "  ) T1" + _
                      "  ON T1.POA = PO.POA" + _
                      "  LEFT JOIN (SELECT DISTINCT PAFN.POA," + _
                      "  FIN.ESTADO, FIN.VERSION VERSION_POA_AVANCE_FIN " + _
                      " FROM  POA_ACTIVIDAD_FINANCIERA PAFN" + _
                      "  LEFT JOIN" + _
                      "  POA_FINANCIERO_PERSONA FIN " + _
                      "     ON FIN.ID_POA_ACTIVIDAD_FINANCIERA = PAFN.ID_POA_ACTIVIDAD_FINANCIERA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIN.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "'" + _
                      "  ) T2" + _
                      " ON T2.POA = PO.POA" + _
                      " WHERE PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
                      " AND '" & usuario & "' IN (PTPE.PERSONA_MONITOREO, " + _
                      " PTPE.JEFE_INMEDIATO,PO.PERSONA_ESPECIALISTA, PM.JEFE_INMEDIATO) " + _
                      " AND PTPE.PERIODO LIKE '" & strPeriodo & "%' " + _
                      " ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function filtrar_asig_admin(ByVal strPeriodo As String, ByVal anhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try

            lxc_SQL = " SELECT PP.ABREVIATURA, " + _
                      " EJ.DESCRIPCION, " + _
                      " PO.NOMBRE, " + _
                      " PO.POA, " + _
                      " PO.ESTADO_REPROG, " + _
                      " VERIFICA_PROY_CONCLUIDO(PO.POA, PO.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " + _
                      " PO.VERSION VERSION_POA_VIGENTE, T1.VERSION_POA_AVANCE_FIS, T2.VERSION_POA_AVANCE_FIN, " + _
                      " PO.MONTO_TOTAL_INVERTIDO," + _
                      " PO.MONTO_INV_ANHIO," + _
                      " UB.NOMBRE UBIGEO," + _
                      " PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO MONITOREADOR, " + _
                      " PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO ESPECIALISTA," + _
                      " G.DESCRIPCION ESTADO_DES," + _
                      " PTPE.ESTADO, " + _
                      " CASE WHEN T1.ESTADO IS NULL " + _
                      "   THEN 'VACIO' " + _
                      "   ELSE " + _
                      "   CASE WHEN T1.ESTADO = '1' " + _
                      "          THEN 'INICIADO'" + _
                      "          ELSE " + _
                      "              CASE WHEN T1.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIS, " + _
                      "     CASE WHEN T2.ESTADO IS NULL" + _
                      "        THEN 'VACIO'" + _
                      "        ELSE" + _
                      "           CASE WHEN T2.ESTADO = '1' " + _
                      "           THEN 'INICIADO'" + _
                      "           ELSE " + _
                      "              CASE WHEN T2.ESTADO = '2' " + _
                      "              THEN 'TERMINADO' " + _
                      "  End" + _
                      "  End" + _
                      "  End" + _
                      "        AS ESTADO_FIN" + _
                      " FROM PLAN_TRABAJO_PERSONA PTPE" + _
                      " INNER JOIN PLAN_TRABAJO_POA PTPO" + _
                      " ON PTPO.ID_PLAN_TRABAJO_PERSONA = PTPE.ID_PLAN_TRABAJO_PERSONA" + _
                      " INNER JOIN POA PO" + _
                      " ON PO.POA = PTPO.POA " + _
                      " INNER JOIN GENERAL G" + _
                      " ON G.CODIGO = PTPE.ESTADO AND G.ID = 'ESTADO'" + _
                      " INNER JOIN PPER PP" + _
                      " ON PP.PPER = PO.PPER" + _
                      " INNER JOIN EJECUTOR EJ" + _
                      " ON EJ.EJECUTOR = PO .EJECUTOR" + _
                      " INNER JOIN PERSONA PM " + _
                      " ON PO.PERSONA_MONITOREO = PM.PERSONA" + _
                      " INNER JOIN PERSONA PE" + _
                      " ON PO.PERSONA_ESPECIALISTA = PE.PERSONA" + _
                      " INNER JOIN UBIGEO UB" + _
                      " ON UB.UBIGEO = PO.UBIGEO  " + _
                      " LEFT JOIN (SELECT DISTINCT PAFS.POA, " + _
                      "  FIS.ESTADO, FIS.VERSION VERSION_POA_AVANCE_FIS " + _
                      "  FROM    POA_ACTIVIDAD_FISICA PAFS" + _
                      "  LEFT JOIN" + _
                      "  POA_FISICO_PERSONA FIS " + _
                      "     ON FIS.ID_POA_ACTIVIDAD_FISICA = PAFS.ID_POA_ACTIVIDAD_FISICA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIS.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "' " + _
                      "  ) T1" + _
                      "  ON T1.POA = PO.POA" + _
                      "  LEFT JOIN (SELECT DISTINCT PAFN.POA," + _
                      "  FIN.ESTADO, FIN.VERSION VERSION_POA_AVANCE_FIN  " + _
                      " FROM    POA_ACTIVIDAD_FINANCIERA PAFN" + _
                      "  LEFT JOIN" + _
                      "  POA_FINANCIERO_PERSONA FIN " + _
                      "     ON FIN.ID_POA_ACTIVIDAD_FINANCIERA = PAFN.ID_POA_ACTIVIDAD_FINANCIERA" + _
                      "     LEFT JOIN PLAN_TRABAJO_POA PTPOA" + _
                      "     ON PTPOA.ID_PLAN_TRABAJO_POA = FIN.ID_PLAN_TRABAJO_POA" + _
                      "     LEFT JOIN PLAN_TRABAJO_PERSONA PTPER" + _
                      "     ON PTPER.ID_PLAN_TRABAJO_PERSONA = PTPOA.ID_PLAN_TRABAJO_PERSONA" + _
                      "     WHERE PTPER.PERIODO = '" & strPeriodo & "'" + _
                      "  ) T2" + _
                      " ON T2.POA = PO.POA" + _
                      " WHERE PO.ANHIO_FIRMA_POA= '" & anhio & "' AND PTPE.ESTADO = '2' " + _
                      " AND PTPE.PERIODO LIKE '" & strPeriodo & "%' " + _
                      " ORDER BY EJ.DESCRIPCION ASC, PO.NOMBRE ASC  "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function filtrar_asig_especial(ByVal usuario As String, ByVal anhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = " select rownum as numero,PTP.ID_PLAN_TRABAJO_POA AS PLAN, PP.ABREVIATURA ,E.DESCRIPCION ,P.POA, P.VERSION, P.NOMBRE,PERD.PERIODO AS CODIGOPERIOD ,PERD.DESCRIPCION AS PERIODO ,P.FECHA_VIABILIDAD,P.MONTO_TOTAL_INVERTIDO,P.MONTO_INV_ANHIO,UB.NOMBRE AS UBIGEO,   (SELECT (PERSONA.NOMBRES  || ' ' ||  PERSONA.apellido_paterno|| ' ' ||  PERSONA.apellido_materno)  FROM PERSONA  WHERE PERSONA.PERSONA= P.PERSONA_MONITOREO) AS MONITOR,(SELECT (PERSONA.NOMBRES  || ' ' ||   PERSONA.apellido_paterno|| ' ' ||  PERSONA.apellido_materno) " + _
                      " FROM PERSONA   WHERE PERSONA.PERSONA=P.PERSONA_ESPECIALISTA)  AS ESPECIALISTA  " + _
                      " from POA P INNER JOIN UBIGEO UB ON P.UBIGEO = UB.UBIGEO " + _
                      " INNER JOIN PERSONA ON  P.PERSONA_MONITOREO = PERSONA.PERSONA " + _
                      " INNER JOIN USUARIO U ON PERSONA.USUARIO = U.USUARIO " + _
                      " left join unidad undd on P.UNIDAD=undd.UNIDAD left join unidad und2 on P.UNIDAD2=und2.UNIDAD  " + _
                      " left join unidad und3 on P.UNIDAD3=und3.UNIDAD INNER JOIN PPER PP ON PP.PPER=P.PPER  " + _
                      " INNER JOIN EJECUTOR E ON E.EJECUTOR=P.EJECUTOR  " + _
                      " INNER JOIN PLAN_TRABAJO_POA PTP ON P.POA= PTP.POA " + _
                      " INNER JOIN PLAN_TRABAJO_PERSONA PTPER ON PTP.ID_PLAN_TRABAJO_PERSONA=PTPER.ID_PLAN_TRABAJO_PERSONA " + _
                      " INNER JOIN PERIODO PERD ON PTPER.PERIODO = PERD.PERIODO  " + _
                      " WHERE '" & usuario & "' IN (PTPER.PERSONA_MONITOREO, P.PERSONA_ESPECIALISTA,PTPER.JEFE_INMEDIATO) and PTPER.ESTADO='2' AND  PERD.MES = (select to_char(sysdate,'MM') from dual) and P.ANHIO_FIRMA_POA= '" & anhio & "'" + _
                      " ORDER BY POA   "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function tipoProyectoActividad() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select TIPO_PROYECTO_ACTIVIDAD, DESCRIPCION from TIPO_PROYECTO_ACTIVIDAD"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ppermonitor() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT PPER,DESCRIPCION,ABREVIATURA FROM PPER"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ppersona() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select persona,NOMBRES || ' ' || APELLIDO_PATERNO || ' ' || APELLIDO_MATERNO from persona inner join USUARIO ON PERSONA.USUARIO = USUARIO.USUARIO where USUARIO.PERFIL='MO'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub edicion_grilla(ByVal TC As String, ByVal TG As String, ByVal IDPOA As String)
        Dim objData As AdministradorOracle

        Dim pObjgrilla As String = "EDITARPLANTRABAJO"
        Try
            objData = New AdministradorOracle
            'Reemplazar el valor que dice 4 por el valor real que ocupa en memoria dicha variable
            objData.CreateInputOracle("TC", TC, 20)
            objData.CreateInputOracle("TG", TG, 20)
            objData.CreateInputOracle("IDPOA", IDPOA, 50)

            objData.ExecuteOracle_NonQuery("EDITARPLANTRABAJO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Sub edicion_finaciero(ByVal V1 As String, ByVal V2 As String, ByVal V3 As String, ByVal V4 As String, ByVal IDPFP As String)
        Dim objData As AdministradorOracle

        Dim pObjgrilla As String = "EDITARAVANCEFINANCIERO"
        Try
            objData = New AdministradorOracle

            'Reemplazar el valor que dice 4 por el valor real que ocupa en memoria dicha variable
            objData.CreateInputOracle("V1", V1, 20)
            objData.CreateInputOracle("V2", V2, 20)
            objData.CreateInputOracle("V3", V3, 20)
            objData.CreateInputOracle("V3", V4, 20)
            objData.CreateInputOracle("IDPFP", IDPFP, 50)

            objData.ExecuteOracle_NonQuery("EDITARAVANCEFINANCIERO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Sub ModificarComentario(ByVal codigo As String, ByVal observacion As String)
        Dim objData As AdministradorOracle

        Try
            objData = New AdministradorOracle
            'Reemplazar el valor que dice 4 por el valor real que ocupa en memoria dicha variable
            objData.CreateInputOracle("cod", codigo, 20)
            objData.CreateInputOracle("obs", observacion, 4000)
            objData.ExecuteOracle_NonQuery("EDITARPLANTRABAJOOBSERVACION", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Function CargarPlan(ByVal strCodigo As String) As String
        Dim strObservacion As String
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try

            lxc_SQL = "SELECT OBSERVACION FROM PLAN_TRABAJO_PERSONA WHERE ID_PLAN_TRABAJO_PERSONA = '" & strCodigo & "'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            strObservacion = objDt.Rows(0).Item("OBSERVACION").ToString
            Return strObservacion
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListarProyectos(ByVal strPersona As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Try
            objData.CreateInputOracle("v_Persona", strPersona, 30)
            objData.CreateInputOracle("v_Ano", strAnhioFirma, 50)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LISTA_PROYECTOS_PERSONA", "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function InsertarPlanTrabajoPersona(ByVal PM As String, ByVal JI As String, ByVal PD As String, ByVal ES As String) As Integer
        Dim objData As AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Dim IDPTP As Integer
        Try
            objData = New AdministradorOracle
            objDt = New DataTable
            lxc_SQL = "select id_plan_trabajo_persona from (select id_plan_trabajo_persona FROM PLAN_TRABAJO_PERSONA order by id_plan_trabajo_persona desc) "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            If objDt.Rows.Count > 0 Then
                lxc_SQL = lxc_SQL + " where rownum=1 "
                objDt = Nothing
                objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            End If
            If objDt.Rows.Count = 0 Then
                IDPTP = 1
            Else
                IDPTP = CInt(objDt.Rows(0).Item("id_plan_trabajo_persona").ToString) + 1
            End If
            objData.CreateInputOracle("IDPTP", IDPTP, 50)
            objData.CreateInputOracle("PM", PM, 8)
            objData.CreateInputOracle("JI", JI, 8)
            objData.CreateInputOracle("PD", PD, 10)
            objData.CreateInputOracle("ES", ES, 1)
            objData.ExecuteOracle_NonQuery("INSERTARPLANTRABAJOPERSONA", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
        Return IDPTP
    End Function

    Public Sub AgregarRevision(ByVal intIdptp As Integer, ByVal obs As String, ByVal estado As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("IDPTP", intIdptp, 50)
            objData.CreateInputOracle("OBS", obs, 4000)
            objData.CreateInputOracle("EST", estado, 1)
            objData.ExecuteOracle_NonQuery("APROBARPLANTRABAJO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub AgregarRevisionInformeMensual(ByVal IntIdInfMensual As Integer, ByVal StrCodPeriodo As String, ByVal StrObsJefe As String, ByVal StrEstado As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("ID_INFORME_MENSUAL_Aux", IntIdInfMensual, 50)
            objData.CreateInputOracle("COD_PERIODO_Aux", StrCodPeriodo, 50)
            objData.CreateInputOracle("OBS_JEFE_Aux", StrObsJefe, 4000)
            objData.CreateInputOracle("ESTADO_Aux", StrEstado, 1)
            objData.ExecuteOracle_NonQuery("APROBAR_INFORME_MENSUAL", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function CargarPlanJefe(ByVal strCodigo As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT JEFE_INMEDIATO, OBSERVACION_JEFE, ESTADO, FECHA_OBSERVACION_JEFE FROM PLAN_TRABAJO_PERSONA WHERE ID_PLAN_TRABAJO_PERSONA = " & strCodigo
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub EliminarPlanTrabajo(ByVal intIdptp As Integer)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("IDPTP", intIdptp, 50)
            objData.ExecuteOracle_NonQuery("ELIMINARPLANTRABAJO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function CargarObservacion(ByVal strPersona As String, ByVal strPeriodo As String) As String
        Dim strObservacion As String
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT OBSERVACION FROM (SELECT  * FROM PLAN_TRABAJO_PERSONA WHERE PERSONA_MONITOREO='" & strPersona & "'" & _
                      "AND PERIODO < '" & strPeriodo & "'  ORDER BY PERIODO DESC) WHERE ROWNUM=1"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            If objDt.Rows.Count > 0 Then
                If objDt.Rows(0).Item("OBSERVACION").ToString = "" Then
                    strObservacion = "Sin Observaciones"
                Else
                    strObservacion = objDt.Rows(0).Item("OBSERVACION").ToString
                End If
            Else
                strObservacion = "Sin Observaciones"
            End If
            Return strObservacion
        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function CargarFechaGeneracion(ByVal strPersona As String, ByVal strPeriodo As String) As String
        Dim strFecha As String
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT FECHA " & _
                    "FROM PLAN_TRABAJO_PERSONA " & _
                    "WHERE PERSONA_MONITOREO='" & strPersona & "' AND " & _
                                " PERIODO = '" & strPeriodo & "' "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            If objDt.Rows.Count > 0 Then
                If objDt.Rows(0).Item("FECHA").ToString = "" Then
                    strFecha = ""
                Else
                    strFecha = objDt.Rows(0).Item("FECHA").ToString
                End If
            Else
                strFecha = ""
            End If
            Return strFecha
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function CargarFechaAprobacion(ByVal strPersona As String, ByVal strPeriodo As String) As String
        Dim strFecha As String
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT FECHA_APROBACION_JEFE " & _
                    "FROM PLAN_TRABAJO_PERSONA " & _
                    "WHERE PERSONA_MONITOREO='" & strPersona & "' AND " & _
                                " PERIODO = '" & strPeriodo & "' "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            If objDt.Rows.Count > 0 Then
                If objDt.Rows(0).Item("FECHA_APROBACION_JEFE").ToString = "" Then
                    strFecha = ""
                Else
                    strFecha = objDt.Rows(0).Item("FECHA_APROBACION_JEFE").ToString
                End If
            Else
                strFecha = ""
            End If
            Return strFecha
        Catch ex As Exception
            Throw ex
        End Try

    End Function

End Class